/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package baiwa.rta.dao.Impl;

import static java.text.MessageFormat.format;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import baiwa.rta.dao.CarCardDao;
import baiwa.rta.dao.mapping.CarCardMapping;
import baiwa.rta.model.DataTableModel;
import baiwa.rta.model.RMaccrModel;
import baiwa.rta.model.TowerModel;
import baiwa.rta.utils.SecurityUtils;

/**
 *
 * @author jirapatjantsakool
 */
@Repository
public class CarCardDaoImpl extends AbstractBaseDao implements CarCardDao {

    @Autowired JdbcTemplate jdbcTemplate;

    private static final String SQL_QUERY = "SELECT * FROM rmaccr ";
    private static final String SQL_QUERY_BY_ID = " SELECT A.* , TW.* FROM RMACCR A  LEFT JOIN RMRMLST TW ON TW.RLRMID =  A.RLRMID  WHERE A.ACCRID = ?";

    @Override
    public List<RMaccrModel> findAll() {

        List<RMaccrModel> list = jdbcTemplate.query(SQL_QUERY, new Object[]{}, new CarCardMapping());

        return list == null ? new ArrayList<RMaccrModel>() : list;

    }

    @Deprecated
    @Override
    public List<Map<String, Object>> findAllMap() {

        return jdbcTemplate.queryForList(SQL_QUERY);

    }

    @Override
    public RMaccrModel findById(Integer accrid) {
    	
        List<RMaccrModel> model = jdbcTemplate.query(SQL_QUERY_BY_ID, new Object[]{accrid}, new CarCardMapping());

        return model == null ? new RMaccrModel() : model.get(0);

    }

    private static final String SQL_INSERT_RMACCR = "INSERT INTO rmaccr (rlrmid,accrlrno,accrcrdid,accridusrno,accrstat,accrstatdat,accratvdat,accrcreby,accrcredat,accrupdby,accrupddat) VALUES(?,?,?,?,? ,?,?,?,CURDATE(),?, CURDATE())";

    @Override
    public void create(final RMaccrModel model) throws Exception {

        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(format(SQL_INSERT_RMACCR), Statement.RETURN_GENERATED_KEYS);
                int i = 0;
                ps.setInt(++i, model.getRlrmid() == null ? null : model.getRlrmid());
                ps.setString(++i, model.getAccrlrno() == null ? null : model.getAccrlrno());
                ps.setString(++i, model.getAccrcrdid() == null ? null : model.getAccrcrdid());
                ps.setString(++i, model.getAccridusrno() == null ? null : model.getAccridusrno());
                ps.setString(++i, model.getAccrstat() == null ? null : model.getAccrstat());
                ps.setDate(++i, model.getAccrstatdat() == null ? null : new java.sql.Date(model.getAccrstatdat().getTime()));
                ps.setDate(++i, model.getAccratvdat() == null ? null : new java.sql.Date(model.getAccratvdat().getTime()));
                ps.setString(++i, SecurityUtils.getUsername());
//                ps.setDate(++i, model.getAccrcredat() == null ? null : new java.sql.Date(model.getAccrcredat().getTime()));
                ps.setString(++i,  SecurityUtils.getUsername());
//                ps.setDate(++i, model.getAccrupddat() == null ? null : new java.sql.Date(model.getAccrupddat().getTime()));
                return ps;
            }

        }, keyHolder);
    }

    private static final String SQL_UPDATE_RMACCR = "UPDATE rmaccr SET rlrmid=?,accrlrno=?,accrcrdid=?,accridusrno=?,accrstat=?,accrstatdat=?,accratvdat=?,accrcreby=?,accrcredat=CURDATE(),accrupdby=?,accrupddat=CURDATE() WHERE accrid=?";

    @Override
    public boolean update(RMaccrModel model) {
        int row = jdbcTemplate.update(format(SQL_UPDATE_RMACCR),
                new Object[]{
                    model.getRlrmid(),
                    model.getAccrlrno(),
                    model.getAccrcrdid(),
                    model.getAccridusrno(),
                    model.getAccrstat(),
                    model.getAccrstatdat(),
                    model.getAccratvdat(),
                    SecurityUtils.getUsername(),
//                    model.getAccrcredat(),
                    SecurityUtils.getUsername(),
//                    model.getAccrupddat(),
                    model.getAccrid()
                });
        return row > 0;
    }

    private static final String SQL_DELETE_RMACCR = "DELETE FROM rmaccr WHERE accrid=?";

    @Override
    public boolean delete(RMaccrModel model) {
        int row = jdbcTemplate.update(format(SQL_DELETE_RMACCR), new Object[]{model.getAccrid()});
        return row > 0;
    }

	@SuppressWarnings("deprecation")
	@Override
	public int nextCarCardId() {
		int n =jdbcTemplate.queryForInt("SELECT COUNT(1)+1 FROM rmaccr");
		return n;
	}

	@SuppressWarnings("deprecation")
	@Override
	public DataTableModel<RMaccrModel> findDataTable(RMaccrModel model,	int page, int perPage) {
		
    	DataTableModel<RMaccrModel> dataTableModel = new DataTableModel<RMaccrModel>();
        String sqlLimit = MessageFormat.format(" limit {0},{1} ", new Object[]{page, perPage});
        
        TowerModel tower = model.getTower();

        /* --- Where Condition  ---*/
        String sql_where = ""; 
        String sql_join = " LEFT JOIN RMRMLST TW ON TW.RLRMID = ";
        /* --- Room Condition  ----*/
        if (StringUtils.isNotBlank(tower.getTower())) {
            sql_where = sql_where.concat(" AND TW.RLSTTOWER = '" + tower.getTower() + "' ");
        }
        if (StringUtils.isNotBlank(tower.getFloor())) {
        	sql_where = sql_where.concat(" AND TW.RLSTFLOOR = '" + tower.getFloor() + "' ");
        }
        if (StringUtils.isNotBlank(tower.getUnitNo())) {
        	sql_where = sql_where.concat(" AND TW.RLSTUNITNO = '" + tower.getUnitNo() + "' ");
        }
        if (StringUtils.isNotBlank(tower.getAddress())) {
        	sql_where = sql_where.concat(" AND TW.RLSTADDR = '" + tower.getAddress() + "' ");
        }
        
        /*--- Other Condition ----*/
        if (StringUtils.isNotBlank(model.getAccridusrno())) {
        	sql_where = sql_where.concat(" AND A.ACCRIDUSRNO LIKE '%" + model.getAccridusrno() + "%' ");
        }
        if (StringUtils.isNotBlank(model.getAccrcrdid())) {
        	sql_where = sql_where.concat(" AND A.ACCRCRDID LIKE '%" + model.getAccrcrdid() + "%' ");
        }
        if (StringUtils.isNotBlank(model.getAccrlrno())) {
        	sql_where = sql_where.concat(" AND A.ACCRLRNO LIKE '%" + model.getAccrlrno() + "%' ");
        }
        if (StringUtils.isNotBlank(model.getAccrstat())) {
            sql_where = sql_where.concat(" AND A.ACCRSTAT = '" + model.getAccrstat() + "' ");
        }
        
         /*--- Initial Query ---*/
		String sqlQuery = " SELECT A.* , TW.* FROM RMACCR A " + sql_join +" A.RLRMID " +
				" WHERE 1=1 " + sql_where + sqlLimit;
		String sqlCount = "SELECT COUNT(1) FROM RMACCR A " + sql_join +" A.RLRMID " +
				" WHERE 1=1"  + sql_where;
		
		// --- #1 Get data for datatable by limit row
		List<RMaccrModel> list = jdbcTemplate.query(sqlQuery, new CarCardMapping());
		
		// --- #2 Get row number
		int count = jdbcTemplate.queryForInt(sqlCount);
		
        dataTableModel.setItems(list);
        dataTableModel.setTotal(count);
        dataTableModel.setIndex(page);
        dataTableModel.setLength(perPage);
        
        System.out.println("SQL : " + sqlQuery);
		return dataTableModel;
	}

}
